Create database  Company 
--创建库
go

Use Company 
--部门信息表
Create Table SectionInfo(
	SectionID int primary key identity,
	SecitonName varchar(10) not null,
)
--员工信息表
Create Table UserInfo(
	UserNO int identity primary key not null ,
	UserName varchar(10) unique not null,
	UserSex varchar(2) not null Check( UserSex in('男','女') ),
	UserAge int not null check(UserAge>=1 and UserAge<=100),
	UserCity varchar(50) default ('福建龙岩市'),
	UserSection int references SectionInfo(SectionID),
	UserSalary decimal(5,2) default(0)
)
--员工考勤表
Create Table WorkInfo(
	WorkID int identity primary key not null ,
	UserID int references UserInfo(UserNO) not null,
	WorkTime datetime not null,
	WorkDescription varchar(40) not null Check(WorkDescription in ('迟到','早退','旷工','病假','事假','出勤'))
	)
--、1.	为部门信息表添加5条记录
Insert into SectionInfo values('户部'),('兵部'),('礼部'),('刑部'),('工部')

--、2.	为员工信息表添加20条记录
insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary) 
values('林付五','男',36,'北京紫禁城',5,200.2),
	  ('林负四','男',36,'北京紫禁城',5,700.2),
	  ('林负三','女',32,'北京紫禁城',4,700.2),
	  ('林负二','男',23,'北京紫禁城',3,700.2),
	  ('林负一','男',43,'北京紫禁城',2,700.2),
	  ('林零一','女',36,'北京紫禁城',1,700.2),
	  ('林林二','女',32,'北京紫禁城',5,700.2),
	  ('林秩三','男',23,'北京紫禁城',4,700.2),
	  ('林开四','男',43,'北京紫禁城',3,700.2),
	  ('林零五','女',36,'北京紫禁城',2,700.2),
	  ('林林六','女',32,'北京紫禁城',1,700.2),
	  ('林秩七','男',23,'北京紫禁城',5,700.2),
	  ('林开八','男',43,'北京紫禁城',4,700.2),
	  ('林零九','女',36,'北京紫禁城',3,700.2),
	  ('林林十','女',32,'北京紫禁城',2,700.2),
	  ('林十一','男',23,'北京紫禁城',1,700.2),
	  ('林十二','男',43,'北京紫禁城',5,700.2),
	  ('林十三','女',36,'北京紫禁城',4,700.2),
	  ('林十四','男',23,'北京紫禁城',3,700.2),
	  ('林十五','男',43,'北京紫禁城',2,700.2),
	  ('林十六','女',36,'北京紫禁城',1,700.2)

insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary) 
values('林付7','男',36,'龙岩安徽市',5,100.20)

select * from UserInfo
--3.	为员工考勤表添加20条记录
insert into WorkInfo(UserId,WorkTime,WorkDescription) 
values(1,'2021-03-06','病假'),
	  (2,'2021-03-07','出勤'),
	  (3,'2021-03-08','事假'),
	  (4,'2021-03-09','早退'),
	  (5,'2021-03-10','旷工'),
	  (6,'2021-03-11','病假'),
	  (7,'2021-03-12','出勤'),
	  (8,'2021-03-13','事假'),
	  (9,'2021-03-14','早退'),
	  (10,'2021-03-15','出勤'),
	  (11,'2021-03-16','出勤'),
	  (12,'2021-03-17','出勤'),
	  (13,'2021-03-18','出勤'),
	  (14,'2021-03-19','早退'),
	  (15,'2021-03-20','早退'),
	  (16,'2021-03-21','早退'),
	  (17,'2021-03-22','出勤'),
	  (18,'2021-03-23','出勤'),
	  (19,'2021-03-24','病假'),
	  (20,'2021-03-25','事假')
select * from WorkInfo
--四、查询数据
--1.	查询公司的部门数量
select count(*) 公司部门数量 from WorkInfo
--2.	查询公司的员工数量
select count(*) 员工数量 from UserInfo 
--3.	查询所有部门的员工数量和工资平均值
select count(*) 员工数量,Avg(UserSalary) 工资平均值 from UserInfo
--4.	查询每个年龄的男女生人数
select UserAge 年龄,UserSex 性别 ,count(*) 人数 from UserInfo  group by UserAge,UserSex
--5.	查询所有部门员工的平均薪资
select UserSection 部门编号 ,Avg(UserSalary) 平均薪资 from UserInfo group by UserSection
 --6.	查询员工最高工资和最低工资的差距
select Max(UserSalary) 最大工资,Min(UserSalary) 最小工资 from UserInfo 
--7.	查询平均工资高于 8000 的部门 id 和它的平均工资.
select UserNO 员工编号，Avg(UserSalary) 平均工资 from UserInfo  group by UserNO Having Avg(UserSalary)>8000
--8.	查询公司员工工资的最大值，最小值，平均值，总和
select Max(UserSalary) 工资最大值,Min(UserSalary) 工资最小值,Avg(UserSalary) 工资平均值,Sum(UserSalary)  工资总和 from UserInfo
--9.	 (有员工的城市)各个城市的平均工资
select UserCity 城市名字,avg(UserSalary) 平均工资 From UserInfo group by UserCity
--10.	查询每个员工本月的出勤情况信息
Select UserID 员工编号, WorkTime 出勤时间,WorkDescription 出勤说明 from WorkInfo group by UserID,WorkTime,WorkDescription
--1)把两张表格合并起查询显示出名字
Select UserInfo.UserName 名字,WorkInfo.WorkDescription 出勤情况 From UserInfo,WorkInfo
--2)两张表格合并查询年龄大于40的人员名单
Select UserInfo.UserNO 人员编号, UserInfo.UserName 名字,UserInfo.UserAge 年龄, WorkInfo.WorkTime 出勤时间 from UserInfo inner join WorkInfo on UserInfo.UserNO=WorkInfo.UserID where  UserInfo.UserAge>40

